package com.jiuyun.dao.impl;

import com.jiuyun.dao.ClassesDao;
import com.jiuyun.entity.Classes;
import com.jiuyun.entity.HomeWork;
import com.jiuyun.entity.Student;
import com.jiuyun.until.AliPool;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class ClassesDaoImpl implements ClassesDao {

    QueryRunner runner = new QueryRunner();

    @Override
    public List<Classes> getClasses() throws SQLException {
        String sql = "select * from classes";
        Connection conn = AliPool.getConn();
        List list =  runner.query(conn,sql,new BeanListHandler<Classes>(Classes.class));
        conn.close();
        return list;
    }

    @Override
    public Classes findclass(String className) throws SQLException {
        String sql = "select * from classes where className=?";
        Connection conn = AliPool.getConn();
        Classes classes =  runner.query(conn,sql,new BeanHandler<Classes>(Classes.class),className);
        conn.close();
        return classes;
    }


    @Override
    public List getstudent(String name) throws SQLException {
        //String sql="select * from student where classid=?";
        String sql = "SELECT student.* FROM student INNER JOIN classes on student.classId = classes.classId where classes.className=?";
        Connection conn = AliPool.getConn();
        List list = runner.query(conn,sql,new BeanListHandler<Student>(Student.class),name);
        conn.close();
        return list;
    }

    @Override
    public List getstud(int classid) throws SQLException {
        String sql="select * from student where classid=?";
        Connection conn = AliPool.getConn();
        List list = runner.query(conn,sql,new BeanListHandler<Student>(Student.class),classid);
        conn.close();
        return list;
    }

    @Override
    public List homework(int classid, int year, int month) throws SQLException {
        String sql="SELECT h.*,s.stname " +
                "FROM student s " +
                "INNER JOIN classes c on c.classId = s.classId " +
                "INNER JOIN homework h on h.stuid = s.stid " +
                "WHERE c.classId = ? and year(ctime)=? and MONTH(ctime)=?";
        Connection conn=AliPool.getConn();
        List list = runner.query(conn,sql,new BeanListHandler<HomeWork>(HomeWork.class),classid,year,month);
        conn.close();
        return list;
    }

    @Override
    public int getpupil(int stid, int classid, String stname, String stpass) throws SQLException {
        String sql = "INSERT into student(stid,classId,stname,stpass) VALUES(?,?,?,?)";
        Connection conn = AliPool.getConn();
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setInt(1,stid);
        pst.setInt(2,classid);
        pst.setString(3,stname);
        pst.setString(4,stpass);
        int ok = pst.executeUpdate();
        return ok;
    }

    @Override
    public int addhomework(HomeWork work) throws SQLException {
        String sql = "INSERT into homework VALUES(null,?,null,1,?,?)";
        Connection conn = AliPool.getConn();
        int n = runner.update(conn,sql,work.getStuid(),work.getCtime(),work.getRemark());
        conn.close();
        return n;
    }

    @Override
    public Student findstudent(int stuid) throws SQLException {
        String sql="select * from student where stid=?";
        Connection conn = AliPool.getConn();
        Student list=runner.query(conn,sql,new BeanHandler<Student>(Student.class),stuid);
        return list;
    }

    @Override
    public int addhomework(int stuid, String data, String bz) throws SQLException {
        String sql="insert into homework values(null,?,1,1,?,?)";
        Connection conn = AliPool.getConn();
        int num=runner.update(conn,sql,stuid,data,bz);
        return num;

    }

}
